/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.Service.DAO;

import com.Service.BO.BusinessBO;
import com.Service.BO.ContactBO;
import com.Service.BO.CountryBO;
import com.Service.BO.DepartmentBO;
import com.Service.BO.DirectoratesBO;
import com.Service.BO.OrganizationBO;
import com.Service.BO.RefValueBO;
import com.Service.BO.SupMaterialsBO;
import com.Service.BO.TeamBO;
import com.Service.BO.UserBO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author olugbokikisemiu
 */
public class SuperDAO {
    
    ConnectionManager util = new ConnectionManager();
    
    public int getUser(String User, String Pass) {
        util.getCon();
        int log = 0;
        String query = "Select UserID from User where Username = '" + User + "' and Password = '" + Pass + "'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            if (rs.next()) {
                log = rs.getInt("UserID");
                
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
            
        }
        return log;
    }
    
    public String getPassword() {
        util.getCon();
        String password = null;
        String query = "Select Password from User";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            if (rs.next()) {
                password = rs.getString("Password");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return password;
    }
    
    public void UpdatePass(UserBO use, String pass) {
        util.getCon();
        String query = "Update User set Password = '" + use.getPass() + "' where Password = '" + pass + "'";
        util.ExecuteNonQuery(query);
    }
    
    public void addContact(ContactBO bean) {
        try {
            util.getCon();
            String query = "Insert into Contact(Firstname, ManagerName, surname, contactType, KnownAs, BestContactMethod, officephone, jobrole, mobilephone,workbase, sthomephone, JobTitle, email, isActive)"
                    + "Values('" + bean.getFirstname() + "', '" + bean.getManagerName() + "', '" + bean.getSurname() + "', '" + bean.getContactType() + "', '" + bean.getKnownas() + "',"
                    + " '" + bean.getBest() + "', '" + bean.getOffice() + "', '" + bean.getJobrole() + "', "
                    + "'" + bean.getMobilephone() + "', '" + bean.getWorkbase() + "', '" + bean.getSthome() + "', '" + bean.getJobTittle() + "', '" + bean.getEmail() + "', "
                    + "'" + bean.getIsactive() + "' )";
            util.ExecuteNonQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void addOrganization(OrganizationBO org) {
        util.getCon();
        String query = "Insert into Organization(OrganName, Description, LeadContact, AddLine1, Addline2, AddLine3, PostCode, City, County, CountryID, "
                + "prefferedOrgan, ExOfInterest, TypeOfBusiness, SicCode, FullDescription, PhoneNumber, FaxNumber, Email, WebAddress, CharityNumber,"
                + "ComNumber, OrganSpecialism, SDisCapabilities, SBarCapabilities, SBenCapabilities, SPCcapabilities, SetCapabilities, Accreditations,"
                + "EOIProgrammes, EOIServices, IsActive) Values('" + org.getName() + "', '" + org.getDesc() + "', '" + org.getLead() + "', '" + org.getAddline1() + "',"
                + "'" + org.getAddline2() + "', '" + org.getAddline3() + "', '" + org.getPostcode() + "', '" + org.getCity() + "', '" + org.getCounty() + "', "
                + "'" + org.getCountry().getID() + "', '" + org.getPreOrgan() + "', '" + org.getExInterest() + "', '" + org.getTypeOfBiz() + "', '" + org.getSicCode() + "',"
                + "'" + org.getFullDesc() + "', '" + org.getFone() + "', '" + org.getFax() + "', '" + org.getEmail() + "', '" + org.getWebAdd() + "', '" + org.getCharity() + "',"
                + "'" + org.getComNumber() + "', '" + org.getOrganSpec() + "', '" + org.getSDisCap() + "', '" + org.getSBarCap() + "', '" + org.getSBenCap() + "',"
                + "'" + org.getSPCcap() + "', '" + org.getSetCap() + "', '" + org.getAccred() + "', '" + org.getEOIProg() + "', '" + org.getEOIService() + "', '" + org.getIsActive() + "')";
        util.ExecuteNonQuery(query);
    }
    
    public List<OrganizationBO> getListOrgan() {
        util.getCon();
        List<OrganizationBO> organ = new ArrayList<OrganizationBO>();
        String Query = "Select OrganName, AddLine1, PostCode, LeadContact, "
                + "IsActive from Organization";
        ResultSet rs = util.ExecuteQuery(Query);
        try {
            while (rs.next()) {
                OrganizationBO org = new OrganizationBO();
                org.setName(rs.getString("OrganName"));
                org.setAddline1(rs.getString("AddLine1"));
                org.setPostcode(rs.getString("PostCode"));
                org.setLead(rs.getString("LeadContact"));
                org.setIsActive(rs.getString("IsActive"));
                organ.add(org);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return organ;
    }
    
    public List<OrganizationBO> getListOfOrgName() {
        util.getCon();
        List<OrganizationBO> organ = new ArrayList<OrganizationBO>();
        String query = "Select * from Organization";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                OrganizationBO niz = new OrganizationBO();
                niz.setName(rs.getString("OrganName"));
                niz.setId(rs.getInt("OrganID"));
                organ.add(niz);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return organ;
    }
    
    public List<RefValueBO> getSpecialism() {
        util.getCon();
        List<RefValueBO> getRef = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '5'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO ref = new RefValueBO();
                ref.setValueID(rs.getInt("ValueID"));
                ref.setValueName(rs.getString("ValueName"));
                getRef.add(ref);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return getRef;
    }
    
    public List<RefValueBO> getServiceDis() {
        util.getCon();
        List<RefValueBO> serve = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '6'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO ref = new RefValueBO();
                ref.setValueID(rs.getInt("ValueID"));
                ref.setValueName(rs.getString("ValueName"));
                serve.add(ref);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return serve;
    }
    
    public List<RefValueBO> getBarriers() {
        util.getCon();
        List<RefValueBO> bar = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '7'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO getbar = new RefValueBO();
                getbar.setValueID(rs.getInt("ValueID"));
                getbar.setValueName(rs.getString("ValueName"));
                bar.add(getbar);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return bar;
    }
    
    public List<RefValueBO> getBenefits() {
        util.getCon();
        List<RefValueBO> ben = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '8'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO getben = new RefValueBO();
                getben.setValueID(rs.getInt("ValueID"));
                getben.setValueName(rs.getString("ValueName"));
                ben.add(getben);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ben;
    }
    
    public List<RefValueBO> getPersonal() {
        util.getCon();
        List<RefValueBO> per = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '9'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO pet = new RefValueBO();
                pet.setValueID(rs.getInt("ValueID"));
                pet.setValueName(rs.getString("ValueName"));
                per.add(pet);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return per;
    }
    
    public List<RefValueBO> getEtnicity() {
        
        util.getCon();
        List<RefValueBO> etc = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '10'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO ret = new RefValueBO();
                ret.setValueID(rs.getInt("ValueID"));
                ret.setValueName(rs.getString("ValueName"));
                etc.add(ret);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return etc;
    }
    
    public List<RefValueBO> getAccredit() {
        util.getCon();
        List<RefValueBO> credit = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '11'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO cred = new RefValueBO();
                cred.setValueID(rs.getInt("ValueID"));
                cred.setValueName(rs.getString("ValueName"));
                credit.add(cred);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return credit;
    }
    
    public List<RefValueBO> getEOIProg() {
        util.getCon();
        List<RefValueBO> eoi = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '12'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO eod = new RefValueBO();
                eod.setValueID(rs.getInt("ValueID"));
                eod.setValueName(rs.getString("ValueName"));
                eoi.add(eod);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return eoi;
    }
    
    public List<RefValueBO> getEOIServe() {
        util.getCon();
        List<RefValueBO> sir = new ArrayList<RefValueBO>();
        String query = "Select * from RefValue where RefID = '13'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                RefValueBO set = new RefValueBO();
                set.setValueID(rs.getInt("ValueID"));
                set.setValueName(rs.getString("ValueName"));
                sir.add(set);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sir;
    }
    
    public List<CountryBO> getListCountry() {
        util.getCon();
        List<CountryBO> count = new ArrayList<CountryBO>();
        String query = "Select * from Country";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                CountryBO con = new CountryBO();
                con.setID(rs.getInt("CountryID"));
                con.setCountryName(rs.getString("CountryName"));
                count.add(con);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return count;
    }
    
    public void saveSupport(SupMaterialsBO sum) {
        util.getCon();
        String query = "Insert into SupportMaterials(URL, Description, Type, AddedBy, AddedDate, OrganID)"
                + " Values('" + sum.getURL() + "', '" + sum.getDesc() + "', '" + sum.getType() + "', '" + sum.getAddedBY() + "','" + sum.getAddedDate() + "', '" + sum.getOrgan().getId() + "')";
        util.ExecuteNonQuery(query);
    }
    
    public List<OrganizationBO> getOrgName() {
        util.getCon();
        List<OrganizationBO> org = new ArrayList<OrganizationBO>();
        String query = "Select OrganName from Organization";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                OrganizationBO organ = new OrganizationBO();
                organ.setName(rs.getString("OrganName"));
                org.add(organ);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return org;
    }
    
    public List<SupMaterialsBO> getSupList() {
        util.getCon();
        List<SupMaterialsBO> sup = new ArrayList<SupMaterialsBO>();
        String query = "Select * from SupportMaterials";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                SupMaterialsBO suppy = new SupMaterialsBO();
                suppy.setURL(rs.getString("URL"));
                suppy.setDesc(rs.getString("Description"));
                suppy.setType(rs.getString("Type"));
                suppy.setAddedBY(rs.getString("AddedBy"));
                suppy.setAddedDate(rs.getString("AddedDate"));
                sup.add(suppy);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sup;
    }
    
    public List<DirectoratesBO> getListDirect() {
        util.getCon();
        List<DirectoratesBO> direct = new ArrayList<DirectoratesBO>();
        String query = "Select * from Directorates";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                DirectoratesBO dir = new DirectoratesBO();
                dir.setId(rs.getInt("DirectID"));
                dir.setName(rs.getString("DirectName"));
                dir.setAddline1(rs.getString("AddLine1"));
                dir.setPostcode(rs.getString("PostCode"));
                dir.setLead(rs.getString("LeadContact"));
                dir.setIsActive(rs.getString("IsActive"));
                direct.add(dir);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return direct;
    }
    
    public List<DepartmentBO> getListDepart() {
        util.getCon();
        List<DepartmentBO> dep = new ArrayList<DepartmentBO>();
        String query = "Select * from Department";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                DepartmentBO deep = new DepartmentBO();
                deep.setId(rs.getInt("DepID"));
                deep.setName(rs.getString("DepName"));
                deep.setAddline1(rs.getString("AddLine1"));
                deep.setPostcode(rs.getString("PostCode"));
                deep.setLead(rs.getString("LeadContact"));
                deep.setIsActive(rs.getString("IsActive"));
                dep.add(deep);
            }
        } catch (Exception e) {
        }
        return dep;
    }
    
    public List<TeamBO> getListTeam() {
        util.getCon();
        List<TeamBO> tem = new ArrayList<TeamBO>();
        String query = "Select * from Team";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                TeamBO team = new TeamBO();
                team.setName(rs.getString("TeamName"));
                team.setAddline1(rs.getString("AddLine1"));
                team.setPostcode(rs.getString("PostCode"));
                team.setLead(rs.getString("LeadContact"));
                team.setIsActive(rs.getString("IsActive"));
                tem.add(team);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tem;
    }
    
    public void saveDirectorates(DirectoratesBO dir) {
        util.getCon();
        String query = "Insert into Directorates(DirectName, DirectDescription, LeadContact, AddLine1, Addline2, AddLine3, PostCode, City,"
                + " county, CountryID, TypeOfBusiness, SicCode, FullDescription, PhoneNumber, FaxNumber, Email, WebAddress, CharityNumber,"
                + "ComNumber, OrganID, IsActive) VALUES('" + dir.getName() + "', '" + dir.getDesc() + "', '" + dir.getLead() + "', '" + dir.getAddline1() + "',"
                + "'" + dir.getAddline2() + "', '" + dir.getAddline3() + "', '" + dir.getPostcode() + "', '" + dir.getCity() + "', '" + dir.getCounty() + "',"
                + "'" + dir.getCountry().getID() + "', '" + dir.getType() + "', '" + dir.getSicCode() + "', '" + dir.getFulldesc() + "', '" + dir.getFone() + "',"
                + "'" + dir.getFax() + "', '" + dir.getEmail() + "', '" + dir.getWebAddress() + "', '" + dir.getCharity() + "', '" + dir.getComnumber() + "', '" + dir.getOrg().getId() + "',"
                + "'" + dir.getIsActive() + "')";
        util.ExecuteNonQuery(query);
    }
    
    public void DepartmentSave(DepartmentBO depart) {
        util.getCon();
        String query = "Insert into Department(DepName, DepDescription, LeadContact, AddLine1, AddLine2, AddLine3, PostCode, City, County, CountryID, TypeOfBusiness,"
                + "SicCode, FullDescription, PhoneNumber, FaxNumber, Email, WebAddress, DirectID, IsActive) Values('" + depart.getName() + "', '" + depart.getDesc() + "',"
                + "'" + depart.getLead() + "', '" + depart.getAddline1() + "', '" + depart.getAddline2() + "', '" + depart.getAddline3() + "', '" + depart.getPostcode() + "',"
                + "'" + depart.getCity() + "', '" + depart.getCounty() + "', '" + depart.getCountry().getID() + "', '" + depart.getTypeofBis() + "', '" + depart.getSicCode() + "',"
                + "'" + depart.getFullDes() + "', '" + depart.getPhone() + "', '" + depart.getFax() + "', '" + depart.getEmail() + "', '" + depart.getWebAdd() + "',"
                + "'" + depart.getDirectID().getId() + "', '" + depart.getIsActive() + "')";
        util.ExecuteNonQuery(query);
    }
    
    public void TeamSave(TeamBO tem) {
        util.getCon();
        String query = "Insert into Team(TeamName, Description, LeadContact, AddLine1, AddLine2, AddLine3, PostCode, City, County, CountryID, TypeOfBusiness,"
                + "SicCode, FullDescription, PhoneNumber, FaxNumber, Email, WebAddress, DepID, IsActive) Values('" + tem.getName() + "', '" + tem.getDesc() + "',"
                + "'" + tem.getLead() + "', '" + tem.getAddline1() + "', '" + tem.getAddline2() + "', '" + tem.getAddline3() + "', '" + tem.getPostcode() + "',"
                + "'" + tem.getCity() + "', '" + tem.getCounty() + "', '" + tem.getCountry().getID() + "', '" + tem.getTypeOfBiz() + "', '" + tem.getSicCode() + "',"
                + "'" + tem.getFullDesc() + "', '" + tem.getPhone() + "', '" + tem.getFax() + "', '" + tem.getEmail() + "', '" + tem.getWebAdd() + "',"
                + "'" + tem.getDep().getId() + "', '" + tem.getIsActive() + "')";
        util.ExecuteNonQuery(query);
        //System.out.println("Query "+ query);
    }

    public List<OrganizationBO> getListAddress() {
        util.getCon();
        List<OrganizationBO> address = new ArrayList<OrganizationBO>();
        String query = "Select CONCAT(o.AddLine1,'  ' , o.AddLine2, '  ' , o.AddLine3 ) AS Address, o.PostCode, o.City, o.County, c.CountryName from Organization o "
                + "join Country c on o.CountryID = c.CountryID";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                
                OrganizationBO add = new OrganizationBO();
                add.setAddline1(rs.getString("Address"));
                add.setPostcode(rs.getString("PostCode"));
                add.setCity(rs.getString("City"));
                add.setCounty(rs.getString("County"));
                add.setAddline2(rs.getString("CountryName"));
                address.add(add);
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return address;
    }
    
    public List<BusinessBO> getBusiness() {
        util.getCon();
        List<BusinessBO> biz = new ArrayList<BusinessBO>();
        String query = "Select * from BusinessType";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                
                BusinessBO bus = new BusinessBO();
                bus.setBisID(rs.getInt("BisID"));
                bus.setBizName(rs.getString("BisName"));
                bus.setSicCode(rs.getInt("SicCode"));
                biz.add(bus);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return biz;
    }
    
    public List<ContactBO> getListContact() {
        util.getCon();
        List<ContactBO> con = new ArrayList<ContactBO>();
        String query = "Select concat(firstname,'  ' ,Surname) as contactname from contact";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                
                ContactBO cont = new ContactBO();
                cont.setFirstname(rs.getString("contactname"));
                con.add(cont);
                
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return con;
    }
    
    public BusinessBO Organization(int id) {
        util.getCon();
        BusinessBO biz = null;
        String query = "Select BisName, SicCode from BusinessType where BisID = '" + id + "'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            if (rs.next()) {
                biz = new BusinessBO();
                biz.setBizName(rs.getString("BisName"));
                biz.setSicCode(rs.getInt("SicCode"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return biz;
    }
    
    public List<OrganizationBO> getOrganByLetter() {
        util.getCon();
        List<OrganizationBO> let = new ArrayList<OrganizationBO>();
        String query = "Select * from Organization where OrganName Between 'A' AND 'E'";
        ResultSet rs = util.ExecuteQuery(query);
        try {
            while (rs.next()) {
                
                OrganizationBO letter = new OrganizationBO();
                letter.setName(rs.getString("OrganName"));
                letter.setAddline1(rs.getString("AddLine1"));
                letter.setPostcode(rs.getString("PostCode"));
                letter.setLead(rs.getString("LeadContact"));
                letter.setIsActive(rs.getString("IsActive"));
                
                let.add(letter);
            }
        } catch (Exception ex) {
            Logger.getLogger(SuperDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return let;
    }
}
